import pandas as pd
import os,pymysql
from log import *

def clear_folder(folder_path):
    """
    清空指定文件夹下的所有文件
    :param folder_path: 要清空的文件夹路径
    """
    for root, dirs, files in os.walk(folder_path):
        for file in files:
            file_path = os.path.join(root, file)
            try:
                if os.path.isfile(file_path):
                    os.remove(file_path)
                    print(f"删除文件 {file_path} 成功")
            except Exception as e:
                print(f"删除文件 {file_path} 时出错: {e}")

def mysql(base):
  # 246数据库
  if base == '154':
    mysql_host = '172.16.91.155'
    mysql_port = 3306
    mysql_user = 'root'
    mysql_passwd = 'deke@2024'
    mysql_database = 'dekeinfo_154'
  conn = pymysql.connect(host=mysql_host, port=mysql_port, user=mysql_user, passwd=mysql_passwd, charset='utf8',db=mysql_database)
  return conn

def read_excel(folder_path):
    # 读取目录下的excel文件，拼成完整路径
    file_name=os.listdir(folder_path)[0]
    file_path=os.path.join(folder_path,file_name)
    logger.info(f'本次下载的文件为： {file_name}')

    # 读取Excel文件
    sheet_name = 'Sheet1'
    df = pd.read_excel(file_path, sheet_name=sheet_name,skiprows=1)
    #按行来遍历，将数据保存到列表中
    data_lists=[]
    for  index,row in df.iterrows():
       data_list=row.values.tolist()
       data_lists.append(data_list)
    logger.info('读取数据成功！')
    return data_lists,file_name,file_path

def write_mysql(data_lists):
    conn = mysql('154')
    cursor = conn.cursor()
    logger.info("数据开始写入，请等待")
    for data in data_lists:
      url=f"https://www.bidcenter.com.cn/news-{data[0]}-4.html"
      title=data[1]
      city=data[2]
      type=data[3]
      release_time=data[4]
      sql = f"INSERT INTO `dekeinfo_154`.`bid_opportunity_temp1` (`id`, `title`, `type`,`url`, `city`, `release_time`, `create_time` ) VALUES (NULL,'{title}','{type}','{url}','{city}','{release_time}',now());"
      cursor.execute(sql)
      conn.commit()
    logger.info("数据写入完成！")

def remove_file(file_path,file_name):
    # 检查是否是文件
    if os.path.isfile(file_path):
      os.remove(file_path)  # 删除文件
      logger.info(f"已删除文件: {file_name}")
